﻿Imports MySql.Data.MySqlClient

Public Class CMaestras

    Private conn As String = My.Settings.Conexion

#Region "Get"

    ''' <summary>
    ''' Retorna listado de ciudades y países activos
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCiudades() As DataSet
        Return Data.ExecuteDataSet(Me.conn, "GetCiudades", Nothing)
    End Function

    ''' <summary>
    ''' Retorna listado de ciudades para llenar un combo
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetListadoCiudades() As DataSet
        Return Data.ExecuteDataSet(Me.conn, "GetListadoCiudades", Nothing)
    End Function

    ''' <summary>
    ''' retorna listado de ciudades que pertenecen a un país
    ''' </summary>
    ''' <param name="idPais"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCiudadesByPais(ByVal idPais As Integer)
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdPais", idPais)
        Return Data.ExecuteDataSet(Me.conn, "GetCiudadesByPais", arrParam)
    End Function

    ''' <summary>
    ''' Retorna listado de la tabla que le llega
    ''' </summary>
    ''' <param name="nombreTabla"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTabla(ByVal nombreTabla As Tabla) As DataSet
        Select Case nombreTabla
            Case Tabla.Moneda
                Return Data.ExecuteDataSet(Me.conn, "GetMonedas", Nothing)
            Case Tabla.Cliente
                Return Data.ExecuteDataSet(Me.conn, "GetClientes", Nothing)
            Case Tabla.Agente
                Return Data.ExecuteDataSet(Me.conn, "GetAgentes", Nothing)
            Case Tabla.Concepto
                Return Data.ExecuteDataSet(Me.conn, "GetConceptos", Nothing)
            Case Tabla.Aeropuerto
                Return Data.ExecuteDataSet(Me.conn, "GetAeropuertos", Nothing)
            Case Tabla.Aerolinea
                Return Data.ExecuteDataSet(Me.conn, "GetAerolineas", Nothing)
            Case Else
                Throw New Exception("No se encontró el procedimiento almacenado '" & nombreTabla & "'")
        End Select
    End Function

    Public Function GetAgenteById(ByVal idAgente As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdAgente", idAgente)
        Return Data.ExecuteDataSet(Me.conn, "GetAgenteById", arrParam)
    End Function

    Public Function GetConceptos(ByVal idUsuario As Integer, ByVal aereo As Boolean, ByVal maritimo As Boolean, ByVal guias As Boolean) As DataSet
        'Construye cadena con los criterios
        Dim criterio As String = ""
        If (aereo) And (maritimo) And (guias) Then
            'Puede consultar todo
            'criterio = " AND ((CONCEPTO.MARITIMO) = 1 OR (CONCEPTO.AEREO = 1) OR (CONCEPTO.TERCEROS = 1)) "
        Else
            If (aereo) Then
                criterio = " AND ((CONCEPTO.AEREO = 1) OR (CONCEPTO.TERCEROS = 1))"
            End If
            If (maritimo) Then
                criterio = " AND ((CONCEPTO.MARITIMO = 1) OR (CONCEPTO.TERCEROS = 1))"
            End If
            If (guias) Then
                criterio = " AND ((CONCEPTO.GUIAS = 1) OR (CONCEPTO.TERCEROS = 1))"
            End If
        End If


        'Construye la consulta que se va a ejecutar
        Dim consulta As String = "  SELECT ID, CONCEPTO, TIPO, VALOR, IVA, MINIMO, AEREO, MARITIMO, TERCEROS, GUIAS, DUEAGENT FROM CONCEPTO WHERE ACTIVO = 1 "
        If (criterio.Length > 0) Then
            consulta = consulta & criterio
        End If
        consulta = consulta & " ORDER BY CONCEPTO;"

        'Se ejecuta la consulta
        Return Data.ExecuteSelect(Me.conn, consulta, Nothing)

    End Function

    ''' <summary>
    ''' Retorna listado de conceptos que aplican al tipo que le llega
    ''' </summary>
    ''' <param name="tipo">"Aereo", "Maritimo", "Terceros"</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetConceptosTipo(ByVal tipo As String) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varTipo", tipo)
        Return Data.ExecuteDataSet(Me.conn, "GetConceptosTipo", arrParam)
    End Function

    ''' <summary>
    ''' Retorna las trms que existen para el dia de hoy
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTRM(ByVal fecha As String) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varFecha", fecha)
        Return Data.ExecuteDataSet(Me.conn, "GetTRM", arrParam)
    End Function

    ''' <summary>
    ''' Retorna toda la info del cliente que le llega
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetClienteById(ByVal id As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdCliente", id)
        Return Data.ExecuteDataSet(Me.conn, "GetClienteById", arrParam)
    End Function

    ''' <summary>
    ''' Retorna los contactos del cliente que le llega
    ''' </summary>
    ''' <param name="cliente">Id del cliente</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContactosCliente(ByVal cliente As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdCliente", cliente)
        Return Data.ExecuteDataSet(Me.conn, "GetContactosCliente", arrParam)
    End Function

    ''' <summary>
    ''' Retorna los clientes que cumplen con los criterios especificados
    ''' </summary>
    ''' <param name="nit"></param>
    ''' <param name="nombre"></param>
    ''' <param name="ciudad"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function BuscarClientes(ByVal nit As String, ByVal nombre As String, ByVal ciudad As Integer) As DataSet
        'Construye cadena con los criterios
        Dim criterio As String = ""
        If (nit.Length > 0) Then
            criterio = criterio & "AND CLIENTE.NIT LIKE ('%" & nit & "%') "
        End If
        If (nombre.Length > 0) Then
            criterio = criterio & "AND CLIENTE.NOMBRE LIKE ('%" & nombre & "%') "
        End If
        If (ciudad > 0) Then
            criterio = criterio & "AND CLIENTE.IDCIUDAD = " & ciudad.ToString
        End If

        'Construye la consulta que se va a ejecutar
        Dim consulta As String = "SELECT CLIENTE.ID, NIT, NOMBRE, CIUDAD.CIUDAD FROM CLIENTE INNER JOIN CIUDAD ON CLIENTE.IDCIUDAD = CIUDAD.ID WHERE CLIENTE.ACTIVO = 1 "
        If (criterio.Length > 0) Then            
            consulta = consulta & criterio
        End If
        'Agrega order by
        consulta = consulta & " ORDER BY NOMBRE;"

        'Se ejecuta la consulta
        Return Data.ExecuteSelect(Me.conn, consulta, Nothing)
    End Function


#End Region

#Region "Actualizar"

    Public Function ActualizarCiudad(ByVal id As Integer, ByVal ciudad As String, ByVal pais As Integer, ByVal abreviatura As String) As String
        Dim arrParam(5) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdCiudad", id)
        arrParam(1) = New MySqlParameter("?varCiudad", ciudad)
        arrParam(2) = New MySqlParameter("?varIdPais", pais)
        arrParam(3) = New MySqlParameter("?varAbreviatura", abreviatura)
        arrParam(4) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(4).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarCiudad", arrParam)
        Return arrParam(4).Value
    End Function

    Public Function ActualizarPais(ByVal id As Integer, ByVal pais As String, ByVal abreviatura As String) As String
        Dim arrParam(4) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdPais", id)
        arrParam(1) = New MySqlParameter("?varPais", pais)
        arrParam(2) = New MySqlParameter("?varAbreviatura", abreviatura)
        arrParam(3) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(3).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarPais", arrParam)
        Return arrParam(3).Value
    End Function

    Public Function ActualizarMoneda(ByVal id As Integer, ByVal moneda As String, ByVal idPais As Integer) As String
        Dim arrParam(4) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdMoneda", id)
        arrParam(1) = New MySqlParameter("?varMoneda", moneda)
        arrParam(2) = New MySqlParameter("?varIdPais", idPais)
        arrParam(3) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(3).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarMoneda", arrParam)
        Return arrParam(3).Value
    End Function

    Public Function ActualizarAeropuerto(ByVal id As Integer, ByVal aeropuerto As String, ByVal idCiudad As Integer) As String
        Dim arrParam(4) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdAeropuerto", id)
        arrParam(1) = New MySqlParameter("?varAeropuerto", aeropuerto)
        arrParam(2) = New MySqlParameter("?varIdCiudad", idCiudad)
        arrParam(3) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(3).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarAeropuerto", arrParam)
        Return arrParam(3).Value
    End Function

    Public Function ActualizarAerolinea(ByVal id As Integer, ByVal aerolinea As String, ByVal abreviatura As String) As String
        Dim arrParam(4) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdAerolinea", id)
        arrParam(1) = New MySqlParameter("?varAerolinea", aerolinea)
        arrParam(2) = New MySqlParameter("?varAbreviatura", abreviatura)
        arrParam(3) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(3).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarAerolinea", arrParam)
        Return arrParam(3).Value
    End Function


    ''' <summary>
    ''' Actualiza la informacion del cliente. Retorna un arraylist de dos posiciones: la primera posicion contiene
    ''' el mensaje del resultado de la actualizaicon del cliente, la segunda posicion contiene el id del cliente
    ''' ya sea que se haya creado o editado
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="nit"></param>
    ''' <param name="nombre"></param>
    ''' <param name="idCiudad"></param>
    ''' <param name="direccion"></param>
    ''' <param name="actividad"></param>
    ''' <param name="telefono"></param>
    ''' <param name="fax"></param>
    ''' <param name="origen"></param>
    ''' <param name="tipo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ActualizarCliente(ByVal id As Integer, ByVal nit As String, ByVal nombre As String, ByVal idCiudad As Integer, ByVal direccion As String, ByVal actividad As String, ByVal telefono As String, ByVal fax As String, ByVal origen As String, ByVal tipo As String) As ArrayList
        Dim arrParam(12) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdCliente", id)
        arrParam(1) = New MySqlParameter("?varNit", nit)
        arrParam(2) = New MySqlParameter("?varNombre", nombre)
        arrParam(3) = New MySqlParameter("?varIdCiudad", idCiudad)
        arrParam(4) = New MySqlParameter("?varDireccion", direccion)
        arrParam(5) = New MySqlParameter("?varActividad", actividad)
        arrParam(6) = New MySqlParameter("?varTelefono", telefono)
        arrParam(7) = New MySqlParameter("?varFax", fax)
        arrParam(8) = New MySqlParameter("?varOrigenRecursos", origen)
        arrParam(9) = New MySqlParameter("?varTipo", tipo)
        arrParam(10) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(10).Direction = ParameterDirection.Output
        arrParam(11) = New MySqlParameter("?nuevoId", MySqlDbType.Int32)
        arrParam(11).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarCliente", arrParam)

        Dim arrRetorno As New ArrayList
        arrRetorno.Add(arrParam(10).Value)
        arrRetorno.Add(arrParam(11).Value)
        Return arrRetorno
    End Function

    Public Function ActualizarConcepto(ByVal id As Integer, ByVal concepto As String, ByVal tipo As String, ByVal valor As Double, ByVal iva As Integer, ByVal minimo As Double, ByVal aereo As Integer, ByVal maritimo As Integer, ByVal terceros As Integer, ByVal guias As Integer, ByVal dueAgent As Integer) As String
        Dim arrParam(12) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdConcepto", id)
        arrParam(1) = New MySqlParameter("?varConcepto", concepto)
        arrParam(2) = New MySqlParameter("?varTipo", tipo)
        arrParam(3) = New MySqlParameter("?varValor", valor)
        arrParam(4) = New MySqlParameter("?varIVA", iva)
        arrParam(5) = New MySqlParameter("?varMinimo", minimo)
        arrParam(6) = New MySqlParameter("?varAereo", aereo)
        arrParam(7) = New MySqlParameter("?varMaritimo", maritimo)
        arrParam(8) = New MySqlParameter("?varTerceros", terceros)
        arrParam(9) = New MySqlParameter("?varGuias", guias)
        arrParam(10) = New MySqlParameter("?varDueAgent", dueAgent)
        arrParam(11) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(11).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarConcepto", arrParam)
        Return arrParam(11).Value
    End Function

    Public Function ActualizarAgente(ByVal id As Integer, ByVal agente As String) As String
        Dim arrParam(3) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdAgente", id)
        arrParam(1) = New MySqlParameter("?varAgente", agente)
        arrParam(2) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(2).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarAgente", arrParam)
        Return arrParam(2).Value
    End Function

    ''' <summary>
    ''' Actualiza la trm para el dia de hoy de las monedas que le llegan
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="moneda1"></param>
    ''' <param name="moneda2"></param>
    ''' <param name="valor"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ActualizarTRM(ByVal id As Integer, ByVal moneda1 As Integer, ByVal moneda2 As Integer, ByVal valor As Double, ByVal fecha As String) As String
        Dim arrParam(6) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdTRM", id)
        arrParam(1) = New MySqlParameter("?varIdMoneda1", moneda1)
        arrParam(2) = New MySqlParameter("?varIdMoneda2", moneda2)
        arrParam(3) = New MySqlParameter("?varValor", valor)
        arrParam(4) = New MySqlParameter("?varFecha", fecha)
        arrParam(5) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(5).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarTRM", arrParam)
        Return arrParam(5).Value
    End Function

    Public Sub ActualizarContacto(ByVal id As Integer, ByVal idCliente As Integer, ByVal nombre As String, ByVal telefono As String, ByVal mail As String, ByVal cargo As String)
        Dim arrParam(6) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdContacto", id)
        arrParam(1) = New MySqlParameter("?varIdCliente", idCliente)
        arrParam(2) = New MySqlParameter("?varNombre", nombre)
        arrParam(3) = New MySqlParameter("?varTelefono", telefono)
        arrParam(4) = New MySqlParameter("?varMail", mail)
        arrParam(5) = New MySqlParameter("?varCargo", cargo)
        Data.ExecuteNonQuery(Me.conn, "ActualizarContacto", arrParam)
    End Sub




#End Region

#Region "Eliminar"

    Public Sub Eliminar(ByVal id As Integer, ByVal tabla As Tabla)
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", id)

        Select Case tabla
            Case CMaestras.Tabla.Ciudad
                Data.ExecuteNonQuery(Me.conn, "EliminarCiudad", arrParam)
            Case CMaestras.Tabla.Pais
                Data.ExecuteNonQuery(Me.conn, "EliminarPais", arrParam)
            Case CMaestras.Tabla.Moneda
                Data.ExecuteNonQuery(Me.conn, "EliminarMoneda", arrParam)
            Case CMaestras.Tabla.Cliente
                Data.ExecuteNonQuery(Me.conn, "EliminarCliente", arrParam)
            Case CMaestras.Tabla.Contacto
                Data.ExecuteNonQuery(Me.conn, "EliminarContacto", arrParam)
            Case CMaestras.Tabla.Concepto
                Data.ExecuteNonQuery(Me.conn, "EliminarConcepto", arrParam)
            Case CMaestras.Tabla.TRM
                Data.ExecuteNonQuery(Me.conn, "EliminarTrm", arrParam)
            Case CMaestras.Tabla.Agente
                Data.ExecuteNonQuery(Me.conn, "EliminarAgente", arrParam)
            Case CMaestras.Tabla.Aeropuerto
                Data.ExecuteNonQuery(Me.conn, "EliminarAeropuerto", arrParam)
            Case CMaestras.Tabla.Aerolinea
                Data.ExecuteNonQuery(Me.conn, "EliminarAerolinea", arrParam)
        End Select

    End Sub

#End Region

    Public Enum Tabla
        Ciudad = 1
        Pais = 2
        Moneda = 3
        Cliente = 4
        Contacto = 5
        Concepto = 6
        TRM = 7
        Agente = 8
        Aeropuerto = 9
        Aerolinea = 10
    End Enum


End Class
